use crate::error::Result;
use crate::{Error, SqliteState};
use rusqlite::{Connection, ToSql};
use serde_json::{Value as JsonValue};
use std::collections::HashMap;
use tauri::State;
use crate::tools::{convert_to_json, get_columns_name, query_data};

/**
 * 连接数据库
 * @param path 数据库路径
 */
#[tauri::command]
pub async fn open(state: State<'_, SqliteState>, path: &str) -> Result<()> {
    let conn =
        Connection::open(path).map_err(|error| Error::DatabaseOpenError(error.to_string()))?;
    let mut connection = state.connection.lock().unwrap();
    connection.insert(path.to_string(), conn);
    Ok(())
}

/**
 * 关闭数据库
 * @param path 数据库路径
 */
#[tauri::command]
pub async fn close(state: State<'_, SqliteState>, path: &str) -> Result<()> {
    let mut connection = state.connection.lock().unwrap();
    let conn = match connection.remove(path) {
        None => return Err(Error::ConnectionError()),
        Some(conn) => conn,
    };
    conn.close()
        .map_err(|(_, error)| Error::DatabaseCloseError(error.to_string()))?;
    Ok(())
}

#[tauri::command]
pub async fn execute(state: State<'_, SqliteState>, path: &str, sql: &str) -> Result<usize> {
    let connection = state.connection.lock().unwrap();
    let conn = match connection.get(path) {
        None => return Err(Error::ConnectionError()),
        Some(conn) => conn,
    };
    let size = conn
        .execute(sql, [])
        .map_err(|error| Error::DatabaseExecuteError(error.to_string()))?;
    Ok(size)
}

#[tauri::command]
pub async fn get_columns(
    state: State<'_, SqliteState>,
    path: &str,
    table: &str,
) -> Result<Vec<String>> {
    let connection = state.connection.lock().unwrap();
    let conn = match connection.get(path) {
        None => return Err(Error::ConnectionError()),
        Some(conn) => conn,
    };
    let stmt = conn
        .prepare(&format!("select * from {};", table))
        .map_err(|error| Error::DatabaseQueryError(error.to_string()))?;
    let result = get_columns_name(&stmt);
    Ok(result)
}


#[tauri::command]
pub async fn insert(state: State<'_, SqliteState>, path: &str, table: &str, data: HashMap<&str, JsonValue>, ) -> Result<usize> {
    let value = convert_to_json(data.values())?;
    let params = value
        .iter()
        .map(|v| v as &dyn ToSql)
        .collect::<Vec<&dyn ToSql>>();
    let connection = state.connection.lock().unwrap();
    let conn = match connection.get(path) {
        None => return Err(Error::ConnectionError()),
        Some(conn) => conn,
    };

    let keys: Vec<&str> = data.keys().copied().collect();
    let placeholder = vec!["?".to_string(); keys.len()].join(",");
    let sql = format!(
        "INSERT INTO {} ({}) VALUES ({})",
        table,
        keys.join(","),
        placeholder
    );

    let size = conn
        .execute(&sql, params.as_slice())
        .map_err(|error| Error::DatabaseExecuteError(error.to_string()))?;
    Ok(size)
}

#[tauri::command]
pub async fn delete(
    state: State<'_, SqliteState>,
    path: &str,
    table: &str,
    filter: &str,
) -> Result<usize> {
    let connection = state.connection.lock().unwrap();

    let conn = match connection.get(path) {
        None => return Err(Error::ConnectionError()),
        Some(conn) => conn,
    };
    let sql = format!("DELETE FROM {} WHERE {}", table, filter);
    conn.execute(&sql, [])
        .map_err(|error| Error::DatabaseExecuteError(error.to_string()))
}


#[tauri::command]
pub async fn update(
    state: State<'_, SqliteState>,
    path: &str,
    table: &str,
    data: HashMap<&str, JsonValue>,
    filter: &str,
) -> Result<usize> {
    let value = convert_to_json(data.values())?;
    let params = value
        .iter()
        .map(|v| v as &dyn ToSql)
        .collect::<Vec<&dyn ToSql>>();
    let keys = data.keys().copied().collect::<Vec<&str>>();
    let placeholder = keys
        .into_iter()
        .map(|key| format!("{} = ?", key))
        .collect::<Vec<String>>()
        .join(", ");
    let sql = format!("UPDATE {} SET {} WHERE {};", table, placeholder, filter);

    let connection = state.connection.lock().unwrap();
    let conn = match connection.get(path) {
        None => return Err(Error::ConnectionError()),
        Some(conn) => conn,
    };
    let size = conn.execute(&sql, params.as_slice())?;
    Ok(size)
}

#[tauri::command]
pub async fn query(state: State<'_, SqliteState>,
                   path: &str,
                   table: &str,
                   filter: &str,
                   columns: Vec<&str>
) -> Result<Vec<HashMap<String, JsonValue>>> {
    let connection = state.connection.lock().unwrap();
    let conn = match connection.get(path) {
        None => return Err(Error::ConnectionError()),
        Some(conn) => conn,
    };
    query_data(conn, table, filter, columns)
}
